﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Data.OleDb;
using System.IO;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BinhSon.Presentation
{
    public static class ProcessExcelFile
    {
        public static DataSet ImportExcelXLS(string fileName, bool hasHeaders)
        {
            var HDR = hasHeaders ? "Yes" : "No";
            string strConn;
            if (fileName.Substring(fileName.LastIndexOf(".")).ToLower() == ".xlsx")
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName +
                          ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
            }
            else
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName +
                          ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
            }
            var output = new DataSet();

            using (var conn = new OleDbConnection(strConn))
            {
                conn.Open();

                var schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables,
                    new object[] {null, null, null, "TABLE"});

                foreach (DataRow schemaRow in schemaTable.Rows)
                {
                    var sheet = schemaRow["TABLE_NAME"].ToString();
                    if (!sheet.EndsWith("_"))
                    {
                        try
                        {
                            var cmd = new OleDbCommand("SELECT * FROM [" + sheet + "]", conn);
                            cmd.CommandType = CommandType.Text;

                            var outputTable = new DataTable(sheet);
                            output.Tables.Add(outputTable);
                            new OleDbDataAdapter(cmd).Fill(outputTable);
                        }
                        catch (Exception ex)
                        {
                            throw new Exception(ex.Message + string.Format("Sheet:{0}.File:F{1}", sheet, fileName, ex));
                        }
                    }
                }
            }
            return output;
        }

        public static void ExportToExcel(string fileName, DataSet data, bool hasHeaders)
        {
            var HDR = hasHeaders ? "Yes" : "No";
            string strConn;
            if (fileName.Substring(fileName.LastIndexOf(".")).ToLower() == ".xlsx")
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName +
                          ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
            }
            else
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName +
                          ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
            }

            using (var conn = new OleDbConnection(strConn))
            {
                conn.Open();

                var adapter = new OleDbDataAdapter();
                var xlsSheet = @"Sheet1$";

                foreach (DataTable table in data.Tables)
                {
                    //create the adapter with the select to get                     
                    adapter.SelectCommand = new OleDbCommand(String.Format("SELECT * FROM [{0}]", xlsSheet), conn);                    

                    var colNames = new string[table.Columns.Count];
                    var colParms = new string[table.Columns.Count];

                    for (var i = 0; i < table.Columns.Count; i++)
                    {
                        colNames[i] = String.Format("[{0}]", table.Columns[i].ColumnName);
                        colParms[i] = "?";
                    }

                    // Create Insert Command
                    adapter.InsertCommand = new OleDbCommand(String.Format("INSERT INTO [{0}] ({1}) values ({2})", xlsSheet, string.Join(",", colNames), string.Join(",", colParms)), conn);

                    // Create Paramaters
                    for (int i = 0; i < table.Columns.Count; i++)
                    {
                        OleDbParameter param = new OleDbParameter(String.Format("@[{0}]", table.Columns[i].ColumnName), OleDbType.Char, 255, table.Columns[i].ColumnName);
                        adapter.InsertCommand.Parameters.Add(param);
                    }
                   
                    // Create the dataset and fill it by using the adapter.                                        
                    adapter.Fill(data);

                    // Call update on the adapter to save all the changes to the dataset
                    //adapter.Update(table);
                }
            }            
        }

        public static void ExportExcelFileTest(string fileName, bool hasHeaders)
        {
            var HDR = hasHeaders ? "Yes" : "No";
            string strConn;
            if (fileName.Substring(fileName.LastIndexOf(".")).ToLower() == ".xlsx")
            {
                strConn = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + fileName +
                          ";Extended Properties=\"Excel 12.0;HDR=" + HDR + ";IMEX=0\"";
            }
            else
            {
                strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + fileName +
                          ";Extended Properties=\"Excel 8.0;HDR=" + HDR + ";IMEX=0\"";
            }

            string xlsSheet = @"Sheet1$";


            using (var conn = new OleDbConnection(strConn))
            {
                conn.Open();

                //OleDbCommand cmd = new OleDbCommand("CREATE TABLE [Sheet1]", conn);
                //cmd.ExecuteNonQuery();

                // Create the dataadapter with the select to get all rows in in the xls
                OleDbDataAdapter adapter = new OleDbDataAdapter("SELECT FirstName, LastName, Age FROM [" + xlsSheet + "]", conn);

                DataSet dataset = new DataSet();

                adapter.Fill(dataset);//, "PersonTable");

                adapter.InsertCommand = new OleDbCommand("INSERT INTO [" + xlsSheet + "] (FirstName, LastName, Age) VALUES (?, ?, ?)", conn);
                adapter.InsertCommand.Parameters.Add("@FirstName", OleDbType.VarChar, 255).SourceColumn = "FirstName";
                adapter.InsertCommand.Parameters.Add("@LastName", OleDbType.Char, 255).SourceColumn = "LastName";
                adapter.InsertCommand.Parameters.Add("@Age", OleDbType.Char, 255).SourceColumn = "Age";

                // *** UPDATE COMMAND ***
                adapter.UpdateCommand = new OleDbCommand("UPDATE [" + xlsSheet + "] SET FirstName = ?, LastName = ?, Age = ?" +

                                                            " WHERE FirstName = ? AND LastName = ? AND Age = ?", conn);
                adapter.UpdateCommand.Parameters.Add("@FirstName", OleDbType.Char, 255).SourceColumn = "FirstName";
                adapter.UpdateCommand.Parameters.Add("@LastName", OleDbType.Char, 255).SourceColumn = "LastName";
                adapter.UpdateCommand.Parameters.Add("@Age", OleDbType.Char, 255).SourceColumn = "Age";
                // For Updates, we need to provide the old values so that we only update the corresponding row.
                adapter.UpdateCommand.Parameters.Add("@OldFirstName", OleDbType.Char, 255, "FirstName").SourceVersion = DataRowVersion.Original;
                adapter.UpdateCommand.Parameters.Add("@OldLastName", OleDbType.Char, 255, "LastName").SourceVersion = DataRowVersion.Original;
                adapter.UpdateCommand.Parameters.Add("@OldAge", OleDbType.Char, 255, "Age").SourceVersion = DataRowVersion.Original;

                // Insert a new row
                DataRow newPersonRow = dataset.Tables[0].NewRow();
                newPersonRow["FirstName"] = "New";
                newPersonRow["LastName"] = "Person";
                newPersonRow["Age"] = "100";
                dataset.Tables[0].Rows.Add(newPersonRow);

                // Updates the first row
                dataset.Tables[0].Rows[0]["FirstName"] = "Updated";
                dataset.Tables[0].Rows[0]["LastName"] = "Person";
                dataset.Tables[0].Rows[0]["Age"] = "55";
                
                // Call update on the adapter to save all the changes to the dataset
                adapter.Update(dataset);
            }            
        }
    }
}
